package com.sfc.sdsr.dao;

import java.util.ArrayList;
import java.util.List;
import com.sfc.sdsr.common.model.Address;
import com.sfc.sdsr.common.model.Event;
import com.sfc.sdsr.common.model.News;
import com.sfc.sdsr.common.model.Person;
import com.sfc.sdsr.common.model.Song;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SqliteDao extends SQLiteOpenHelper {

    private final String TAG = getClass().getSimpleName();
    private DataToJava dataToJava; 
	
	public SqliteDao(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public void importAddresses(List<Address> addresses) {
        SQLiteDatabase sld = getWritableDatabase();
        ContentValues contentValues = null;

        for (Address address : addresses) {
            contentValues = new ContentValues();
            
            contentValues.put(Columns.ADDRESS_addressUuid.getName(), address.getAddressUuid().toString());
            contentValues.put(Columns.ADDRESS_city.getName(), address.getCity());
            contentValues.put(Columns.ADDRESS_commonName.getName(), address.getCommonName());
            contentValues.put(Columns.ADDRESS_extraString.getName(), address.getExtraString());
            contentValues.put(Columns.ADDRESS_houseNumber.getName(), address.getHouseNumber());
            contentValues.put(Columns.ADDRESS_modifier.getName(), address.getModifier());
            contentValues.put(Columns.ADDRESS_state.getName(), address.getState());
            contentValues.put(Columns.ADDRESS_streetName.getName(), address.getStreetName());
            contentValues.put(Columns.ADDRESS_zip.getName(), address.getZip());
            
            try {
                sld.insertOrThrow(TableNames.ADDRESS.getTableName(), null, contentValues);
            } catch (SQLException se) {
                Log.w(TAG, se.getMessage());
            }
        }
        
        sld.close();
	}
	
	public void importNews(List<News> news) {
		SQLiteDatabase sld = getWritableDatabase();
		ContentValues contentValues = null;

		for (News currentNews : news) {
			contentValues = new ContentValues();

			String sql = "select pk_personId from Person where personUuid = '" + currentNews.getAuthor().getPersonUuid() + "'";
			Cursor cursor = getReadableDatabase().rawQuery(sql, null);
            int columnIndex = cursor.getColumnIndex("pk_personId");
            cursor.moveToPosition(columnIndex);
            int authorIndex = cursor.getInt(columnIndex);
			cursor.close();
			
			contentValues.put(Columns.NEWS_fk_author.getName(), authorIndex);
			contentValues.put(Columns.NEWS_article.getName(), currentNews.getArticle());
			contentValues.put(Columns.NEWS_headline.getName(), currentNews.getHeadline());
			contentValues.put(Columns.NEWS_newsUuid.getName(), currentNews.getNewsUuid().toString());
			contentValues.put(Columns.NEWS_reportDate.getName(), currentNews.getUtcReportDate());
			try {
	            sld.insertOrThrow(TableNames.NEWS.getTableName(), null, contentValues);
            } catch (SQLException se) {
                Log.w(TAG, se.getMessage());
            }
		}

		sld.close();
	}

	public void importSongs(List<Song> songs) {
		SQLiteDatabase sld = getWritableDatabase();
		ContentValues contentValues = null;

		for (Song song : songs) {
			contentValues = new ContentValues();

			String sql = "select pk_keySignatureId from KeySignature where keySignature = '" + song.getKeySignature().toString() + "'";
			Cursor cursor = getReadableDatabase().rawQuery(sql, null);
            int columnIndex = cursor.getColumnIndex("pk_keySignatureId");
            cursor.moveToPosition(columnIndex);
            int keySignatureIndex = cursor.getInt(columnIndex);
			cursor.close();

			contentValues.put(Columns.SONG_fk_keySignature.getName(), keySignatureIndex);
			contentValues.put(Columns.SONG_artist.getName(), song.getArtist());
			contentValues.put(Columns.SONG_bpm.getName(), song.getBpm());
			contentValues.put(Columns.SONG_extraString.getName(), song.getExtraString());
			contentValues.put(Columns.SONG_genra.getName(), song.getGenra());
			contentValues.put(Columns.SONG_songUuid.getName(), song.getSongUuid().toString());
			contentValues.put(Columns.SONG_title.getName(), song.getTitle());

			try {
			    sld.insertOrThrow(TableNames.SONG.getTableName(), null, contentValues);
            } catch (SQLException se) {
                Log.w(TAG, se.getMessage());
            }
		}

		sld.close();
	}

	public void importPeople(List<Person> people) {
		SQLiteDatabase sld = getWritableDatabase();
		ContentValues contentValues = null;

		for (Person person : people) {
			contentValues = new ContentValues();

			String sql = "select pk_addressId from Address where addressUuid = '" + person.getAddress().getAddressUuid().toString() + "'";
			Cursor cursor = getReadableDatabase().rawQuery(sql, null);
			int columnIndex = cursor.getColumnIndex("pk_addressId");
			cursor.moveToPosition(columnIndex);
            int addressIndex = cursor.getInt(columnIndex);
			cursor.close();

			sql = "select pk_occupationId from Occupation where occupation = '" + person.getOccupation().toString() + "'";
			cursor = getReadableDatabase().rawQuery(sql, null);
			columnIndex = cursor.getColumnIndex("pk_occupationId");
			cursor.moveToPosition(columnIndex);
			int occupationIndex = cursor.getInt(columnIndex);
			cursor.close();

			sql = "select pk_sfcRoleId from SfcRole where sfcRole = '" + person.getSfcRole().toString() + "'";
			cursor = getReadableDatabase().rawQuery(sql, null);
			columnIndex = cursor.getColumnIndex("pk_sfcRoleId");
			cursor.moveToPosition(columnIndex);
			int sfcRoleIndex = cursor.getInt(columnIndex);
			cursor.close();

			contentValues.put(Columns.PERSON_birthDate.getName(), person.getUtcBirthDate());
			contentValues.put(Columns.PERSON_extraString.getName(), person.getExtraString());
			contentValues.put(Columns.PERSON_firstName.getName(), person.getFirstName());
			contentValues.put(Columns.PERSON_fk_address.getName(), addressIndex);
			contentValues.put(Columns.PERSON_fk_occupation.getName(), occupationIndex);
			contentValues.put(Columns.PERSON_fk_sfcRole.getName(), sfcRoleIndex);
			contentValues.put(Columns.PERSON_joined.getName(), person.getUtcJoinDate());
			contentValues.put(Columns.PERSON_lastName.getName(), person.getLastName());
			contentValues.put(Columns.PERSON_middleName.getName(), person.getMiddleName());
			contentValues.put(Columns.PERSON_nickName.getName(), person.getNickName());
			contentValues.put(Columns.PERSON_personUuid.getName(), person.getPersonUuid().toString());

			try {
			    sld.insertOrThrow(TableNames.PERSON.getTableName(), null, contentValues);
            } catch (SQLException se) {
                Log.w(TAG, se.getMessage());
            }
		}

		sld.close();
	}

	public void importEvents(List<Event> events) {
		SQLiteDatabase sld = getWritableDatabase();
		ContentValues contentValues = null;

		for (Event event : events) {
			contentValues = new ContentValues();

			String sql = "select pk_addressId from Address where addressUuid = '" + event.getAddress().getAddressUuid() + "'";
			Cursor cursor = getReadableDatabase().rawQuery(sql, null);
            int columnIndex = cursor.getColumnIndex("pk_addressId");
            cursor.moveToPosition(columnIndex);
            int addressIndex = cursor.getInt(columnIndex);
			cursor.close();

			sql = "select pk_personId from Person where personUuid = '" + event.getOrganizer().getPersonUuid() + "'";
			cursor = getReadableDatabase().rawQuery(sql, null);
            columnIndex = cursor.getColumnIndex("pk_personId");
            cursor.moveToPosition(columnIndex);
			int organizerIndex = cursor.getInt(columnIndex);
			cursor.close();

			contentValues.put(Columns.EVENT_eventDate.getName(), event.getUtcEventDate());
			contentValues.put(Columns.EVENT_eventName.getName(), event.getEventName());
			contentValues.put(Columns.EVENT_eventUuid.getName(), event.getEventUid().toString());
			contentValues.put(Columns.EVENT_extraString.getName(), event.getExtraString());
			contentValues.put(Columns.EVENT_fk_address.getName(), addressIndex);
			contentValues.put(Columns.EVENT_fk_organizer.getName(), organizerIndex);
			contentValues.put(Columns.EVENT_reason.getName(), event.getReason());
			contentValues.put(Columns.EVENT_webpage.getName(), event.getWebpage().toString());

			try {
			    sld.insertOrThrow(TableNames.EVENT.getTableName(), null, contentValues);
            } catch (SQLException se) {
                Log.w(TAG, se.getMessage());
            }
		}

		sld.close();
	}


	public List<News> getNews() {
	    List<News> news = new ArrayList<News>();
	    SQLiteDatabase sld = getReadableDatabase();

	    Cursor cursor = sld.rawQuery("select * from News", null);

	    cursor.moveToFirst();
	    
	    News newNews = null;
	    while(!cursor.isAfterLast()) {
	        newNews = new News();
	        
	        newNews = dataToJava.getNews(cursor);
	        
	        news.add(newNews);
	        cursor.moveToNext();
	    }
	    
	    cursor.close();
	    sld.close();
        return news;
	}

	public List<Event> getEvents() {
	    List<Event> events = new ArrayList<Event>();
        SQLiteDatabase sld = getReadableDatabase();

        Cursor cursor = sld.rawQuery("select * from Event", null);

        cursor.moveToFirst();
        
        Event newEvent = null;
        while(!cursor.isAfterLast()) {
            newEvent = new Event();
            
            newEvent = dataToJava.getEvent(cursor);
            
            events.add(newEvent);
            cursor.moveToNext();
        }
        
        cursor.close();
        sld.close();
        return events;
	}

	public List<Song> getSongs() {
	    List<Song> songs = new ArrayList<Song>();
        SQLiteDatabase sld = getReadableDatabase();

        Cursor cursor = sld.rawQuery("select * from Song", null);

        cursor.moveToFirst();
        
        Song newSong = null;
        while(!cursor.isAfterLast()) {
            newSong = new Song();
            
            newSong = dataToJava.getSong(cursor);
            
            songs.add(newSong);
            cursor.moveToNext();
        }
        
        cursor.close();
        sld.close();
        return songs;
	}

	public List<Person> getMembers() {
	    List<Person> people = new ArrayList<Person>();
        SQLiteDatabase sld = getReadableDatabase();

        Cursor cursor = sld.rawQuery("select * from Person", null);

        cursor.moveToFirst();
        
        Person newPerson = null;
        while(!cursor.isAfterLast()) {
            newPerson = new Person();
            
            newPerson = dataToJava.getPerson(cursor);
            
            people.add(newPerson);
            cursor.moveToNext();
        }
        
        cursor.close();
        sld.close();
        return people;
	}

    public void setDataToJava(DataToJava dataToJava) {
        this.dataToJava = dataToJava;
    }
}